package com.gopay.cron.service.trans.impl;

import com.gopay.common.PageInfo;
import com.gopay.common.constants.approval.DecisionValue;
import com.gopay.common.constants.cps.CpsGenPayOrderConstants;
import com.gopay.common.constants.trans.GatewaySource;
import com.gopay.common.constants.trans.OrderType;
import com.gopay.common.constants.trans.PayChannel;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.common.constants.txncd.IntTxnCd;
import com.gopay.common.datasource.DBType;
import com.gopay.common.datasource.DbContextHolder;
import com.gopay.common.exception.GopayException;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.cron.event.handler.CreateEventDefaultHandler;
import com.gopay.cron.service.trans.TransQueryService;
import com.gopay.cron.web.util.DicTranslate;
import com.gopay.cron.web.vo.TransQueryParamVO;
import com.gopay.cron.web.vo.TransQueryResultVO;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;


/**
 * TransQueryServiceImpl.java
 *
 * @author pengxw
 * @2013-5-22 上午9:25:44 www.gopay.com.cn Inc.All rights reserved.
 */
@Service("transQueryService")
public class TransQueryServiceImpl implements TransQueryService {

    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
    private Logger log = LoggerFactory.getLogger(CreateEventDefaultHandler.class);

    /**
     * 查询
     *
     * @param paramVo
     * @param pageInfo
     * @return
     * @throws GopayException
     */
    @Override
    public List<TransQueryResultVO> query(TransQueryParamVO paramVo, PageInfo pageInfo) throws GopayException {
        DbContextHolder.setDbType(DBType.DS_ONLY_READ.value);
        // 封装查询参数
        Map<String, Object> map = buildQueryParams(paramVo);

        // 封装查询Sql
        String sql = buildQuerySql(paramVo);

        // 执行查询
//        List<Object[]> rsList = commonQueryDAO.findBySql(sql, map, pageInfo.getPageNum(), pageInfo.getPageSize());
        List<Object[]> rsList = commonQueryDAO.findBySql(sql, map, pageInfo.getPageNum(), pageInfo.getPageSize());


        // 封装查询结果
        return buildQueryResult(rsList);
    }


    /**
     * 封装查询参数
     *
     * @param paramVo
     * @return
     */
    private Map<String, Object> buildQueryParams(TransQueryParamVO paramVo) {
        Map<String, Object> map = new HashMap<String, Object>();

        if (StringUtils.isNotBlank(paramVo.getChangeNum())) {// 国付宝订单号
            map.put("gopayOrderId", paramVo.getChangeNum());
        }
        return map;
    }

    /**
     * 封装查询Sql
     *
     * @param paramVo
     * @return
     */
    private String buildQuerySql(TransQueryParamVO paramVo) {
        StringBuilder sql = new StringBuilder("");

        sql.append(" select m.GOPAY_TXN_TM, ");//0
        sql.append(" m.GOPAY_ORDER_ID, ");//1
        sql.append(" m.GOPAY_INT_TXN_CD, ");//2
        sql.append(" m.MER_ID, ");//3
        sql.append(" m.MER_ORDER_ID, ");//4
        sql.append(" m.MER_TXN_AMT, ");//5
        sql.append(" m.ORDER_REAL_FEE_AMT, ");//6
        sql.append(" m.OUT_STLM_ID, ");//7
        sql.append(" m.ORDER_STLM_TIME, ");//8
        sql.append(" m.PAY_CHANNEL, ");//9
        sql.append(" m.GATEWAY_SOURCE, ");//10
        sql.append(" m.TXN_STA_CD, ");
        sql.append(" m.REVERSAL_FLAG, ");
        /*console优化 新交易查询退款交易显示原银行订单号 added by Chenyu Li at 2017/12/14 start*/
        sql.append("    case" +
                "          when (m.relevance_gopay_order_id is not null and m.gopay_int_txn_cd in ('01200','01201','01202','01203','00701','01610','02012','02015')) then" +
                "           (select bi3.gopay_out_order_id from bis_bank_main_order bi3 where bi3.gopay_order_id = m.relevance_gopay_order_id)" +
                "          else" +
                "           bo.gopay_out_order_id" +
                "        end AS gopay_out_order_id, ");
        /*console优化 新交易查询退款交易显示原银行订单号 added by Chenyu Li at 2017/12/14 end*/
        sql.append(" bi.NAME, ");
        sql.append(" c1.CUST_NAME payCustName, ");
        sql.append(" c1.EMAIL payEmail, ");
        sql.append(" m.pay_acct, ");
        sql.append(" c2.CUST_NAME recvCustName, ");
        sql.append(" c2.EMAIL recvEmail, ");
        sql.append(" m.main_recv_acct, ");
        sql.append(" c3.CUST_NAME merName, ");
        sql.append(" a.prod_code, ");
        sql.append(" nvl(wav.STEP_NUM, -1), ");
        sql.append(" nvl(wav.DECISION, -1), ");
        sql.append(" nvl(wav.DONE, -1), ");
        sql.append(" m.child_tran_type, ");
        sql.append(" m.mer_txn_tm, ");
        //付款渠道[线上(自动)、线上、线下]
        sql.append(" case when(bo.GOPAY_OUT_ORDER_ID is not null and (gpo.AUTO_SETTLE is null or gpo.AUTO_SETTLE = '"+CpsGenPayOrderConstants.AUTO_SETTLE_NO+"')) then '02' ");
        sql.append(" when gpo.AUTO_SETTLE = '"+CpsGenPayOrderConstants.AUTO_SETTLE_YES+"' then '01' ");
        sql.append(" else (case when(gpo.OFFLINE_BANK_ORDER_NUM is not null or (wav.STEP_NUM = 2 and wav.DECISION =1)) then '03' else '' end ) end AS payType,");
        sql.append(" m.remark1, ");
        sql.append(" ga.refund_fee_amt, ");
        sql.append(" u.USER_SOURCE,");
        sql.append(" m.TOTAL_ORDER_AMT, ");
        //金额修改-增加结算金额和手续费承担方字段-jiaoshuai
        sql.append(" m.SETTLE_AMT,"); //结算金额
        sql.append(" m.ORDER_FEE_PAYER, ");//手续费承担方
        sql.append(" gpo.RECV_BANK_NAME as RECV_BANK_NAME, ");  //付款银行  add by zhaojt
        sql.append(" m.order_type as orderType, ");  //订单类型  add by zhaojt
        sql.append(" bim.NAME as offline_bank_name, ");  //线下付款银行  add by zhaojt
        sql.append(" mv2.BANK_NAME as ORI_RECV_BANK_NAME, ");    //原交易银行  add by zhaojt
        sql.append(" bi2.NAME as BI_BANK_NAME, ");    //银行订单  add by zhaojt
        sql.append(" cg.pay_channel as oldPayChannel, ");    //原支付方式  add by zhaoxubing
        sql.append(" bo.TRANS_BATCH_NO, ");
        sql.append(" bi3.NAME as NETS_UNION_RECV_BANK_NAME, ");
        sql.append(" bi4.NAME as NETS_UNION_PAY_BANK_NAME ");


        sql.append(" from CPS_GEN_MAIN_ORDER m ");
        sql.append("  LEFT JOIN CPS_GEN_MAIN_ORDER cg on m.relevance_gopay_order_id=cg.gopay_order_id ");
        sql.append(" left join MAIN_ORDER_RECEIPT_VIEW mv2 on m.RELEVANCE_GOPAY_ORDER_ID = mv2.gopay_order_id ");
        sql.append(" left join bis_bank_main_order bo on bo.gopay_order_id = m.gopay_order_id ");
        sql.append(" left join bis_bank_main_order bo2 on bo2.gopay_order_id = m.RELEVANCE_GOPAY_ORDER_ID ");
        sql.append(" left join dic_gopay_bank_info bi on m.out_stlm_id = bi.ABBR_CODE ");
        sql.append(" left join dic_gopay_bank_info bi2 on bo.GOPAY_BANK_CODE = bi2.ABBR_CODE ");
        sql.append(" left join dic_gopay_bank_info bi3 on bo.RECV_BANK = bi3.ABBR_CODE ");
        sql.append(" left join dic_gopay_bank_info bi4 on bo2.RECV_BANK = bi4.ABBR_CODE ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c1 on c1.CUST_ID = m.pay_cust_id ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c2 on c2.CUST_ID = m.main_recv_cust_id ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c3 on c3.CUST_ID = m.mer_id ");
        sql.append(" left join ACPS_ACCT_MAST a on a.acct_id = m.pay_acct ");
        sql.append(" left join ACPS_ACCT_MAST a1 on a1.acct_id = m.main_recv_acct ");
        sql.append(" left join CPS_GEN_ASSIST_ORDER ga on ga.gopay_order_id = m.gopay_order_id ");
        sql.append(" left join (select v.target_id, ");
        sql.append("                   v.step_num, ");
        sql.append("                   v.decision, ");
        sql.append("                   v.done, ");
        sql.append("                   v.task_id, ");
        sql.append("                   max(v.task_id) OVER(PARTITION BY v.target_id) maxTaskId ");
        sql.append("            from WF_APPR_VIEW v) wav on (wav.target_id = m.gopay_order_id and ");
        sql.append("                                wav.task_id >= wav.maxTaskId) ");
        sql.append(" left join CPS_GEN_PAY_ORDER gpo on m.gopay_order_id = gpo.gopay_order_id ");//新增付款渠道条件
        sql.append(" left join dic_gopay_bank_info bim on gpo.offline_gopay_bank_code = bim.ABBR_CODE ");//新增线下付款银行
        //一帐通 新增 客户来源 (2015/12/9)
        sql.append(" left join USER_INFO u on u.CUST_ID = m.pay_cust_id ");
        sql.append(" where 1 = 1 ");
        sql.append("and m.gopay_int_txn_cd not like '017%'  and (nvl(a1.prod_code,0)!= 'KJRMBZH'  and nvl(a.prod_code,0)!= 'KJRMBZH' and  nvl(a1.prod_code,0)!= 'KJWKZH'  and nvl(a.prod_code,0)!= 'KJWKZH'  )");
        //屏蔽掉跨境的订单,add by qld 20161008,跨境的订单就不用进入读写分离的表了
        if (StringUtils.isNotBlank(paramVo.getChangeNum())) {// 国付宝订单号
            sql.append(" and m.gopay_order_id=:gopayOrderId ");
        }
        // 排序从句
      //  sql.append("  order by m.GOPAY_TXN_TM desc ");
        return sql.toString();
    }

    /**
     * 封装查询结果
     *
     * @param rsList
     * @return
     */
    private List<TransQueryResultVO> buildQueryResult(List<Object[]> rsList) {
        List<TransQueryResultVO> orderList = new ArrayList<TransQueryResultVO>();

        String dateFormat = "yyyy-MM-dd HH:mm:ss";

        TransQueryResultVO r;
        for (Object[] arr : rsList) {
            r = new TransQueryResultVO();
            r.setTranFinishTime(DateUtils.format((Date) arr[0], dateFormat));
            r.setGopayOrderId((String) arr[1]);
            r.setTransTypeId((String) arr[2]);
            r.setTransType(IntTxnCd.getIntTxnCdName(r.getTransTypeId()));//生产bug5期 优化 将DicTranslate 类中的交易码翻译替换为 IntTxnCd中的翻译 lx 2017-6-15 12:27:46
            r.setMerId((String) arr[3]);
            r.setMerOrderId((String) arr[4]);
            if(arr[5]!=null){
                r.setOrderDealAmt(((BigDecimal)arr[5]).setScale(2, RoundingMode.HALF_UP).toString());
            }
            if(arr[6]!=null){
                r.setOrderRealFeeAmt(((BigDecimal)arr[6]).setScale(2, RoundingMode.HALF_UP).toString());
            }
            if(arr[8]!=null){
                r.setOrderStlmTime(DateUtils.format((Date) arr[8], dateFormat));
            }
            r.setPayChannel((String) arr[9]);
            r.setPayChannel2(DicTranslate.getPayChannelName(r.getPayChannel()));
            if(arr[11]!=null){
                r.setTransStatusId(((BigDecimal)arr[11]).toString());
                r.setTransStatus(TxnStaCode.getCategory(Integer.valueOf(r.getTransStatusId())));
            }
            if(arr[12]!=null){
                r.setReversalFlag(((BigDecimal)arr[12]).toString());
            }
            //银行订单号
            if(arr[13]!=null){
                r.setGopayOutOrderId((String)arr[13]);
            }else{
                if(IntTxnCd._01302.value.equals(r.getTransTypeId())&&
                        (PayChannel._01.value.equals(r.getPayChannel())||PayChannel._51.value.equals(r.getPayChannel()))){
                    //获取银行订单号
                    String bankOrderId=getBankOrderId(r.getGopayOrderId());
                    r.setGopayOutOrderId(bankOrderId);
                }
            }

            initRecvPayBankName(r, arr);

            if(arr[15]!=null){
                r.setPayCustName((String)arr[15]);
            }
            if(arr[16]!=null){
                r.setPayCusEmail((String)arr[16]);
            }
            if(arr[17]!=null){
                r.setPayAcct((String)arr[17]);
            }

            if(arr[18]!=null){
                r.setRecCusName((String)arr[18]);
            }
            if(arr[19]!=null){
                r.setRecCusMail((String)arr[19]);
            }
            if(arr[20]!=null){
                r.setMainRecvAcct((String)arr[20]);
            }
            if(arr[21]!=null){//商户名称
                r.setTransAdr((String)arr[21]);
            }
            if(arr[22]!=null){//付款方账户产品
                r.setAcctType((String)arr[22]);
            }

            //审核相关
            int stepNum=((BigDecimal)arr[23]).intValue();
            int decision=((BigDecimal)arr[24]).intValue();
            int done=((BigDecimal)arr[25]).intValue();
            //订单处理状态
            r.setHanderStatus(translateOrderHandleSts(stepNum,decision,done,Integer.valueOf((null == r.getTransStatusId()?"0":r.getTransStatusId()))));

            //子交易类型
            r.setChildTranType((String)arr[26]);
            //商户交易时间
            r.setMerTranTime(DateUtils.format((Date) arr[27], dateFormat));

            //是否为完成订单,用来显示补单按钮
            if("00100,00400,00200,00201,00202,02301".indexOf(r.getTransTypeId())>-1
                    &&String.valueOf((TxnStaCode.TXN_STA_CD_20000.value)).equals(r.getTransStatusId())){
            	if(!"25".equals(r.getPayChannel())){//条码支付不需要补单，xuangh
            		 //1-需要补单
                    r.setCompleteOrder("1");
            	}
            }else{
                //2-不需要补单
                r.setCompleteOrder("2");
            }
            //付款渠道[线上(自动)、线上、线下]
            if(arr[28]!=null){
                r.setPayType((String)arr[28]);
                if("01".equals((String)arr[28])){//自动结算时，把处理状态置空
                    r.setHanderStatus(null);
                }
            }

            //支付来源
//            if(GatewayMobileMarkConst.MOBILE_WAP.equals((String)arr[29])
//                &&GatewaySource.GATEWAY_SOURCE_02.value.equals((String)arr[10])){
//                r.setPaySource("移动端");
//            }else{
//                r.setPaySource("PC端");
//            }
//            if(GatewayMobileMarkConst.MOBILE_WAP.equals((String)arr[29])
//                    &&GatewaySource.GATEWAY_SOURCE_02.value.equals((String)arr[10])){
//            log.info("-------------订单号"+(String) arr[1]+"---------网关来源："+(String) arr[10]);
            //GatewaySource=02即可表示移动端  ,移动网关为移动端 GatewaySource=10 表示G商融通移动端
            if (GatewaySource.GATEWAY_SOURCE_02.value.equals((String) arr[10])
					|| GatewaySource.GATEWAY_SOURCE_13.value.equals((String) arr[10])
                    // 微信app支付也改为02  update by dongdh 20160816
                    || GatewaySource.GATEWAY_SOURCE_10.value.equals((String)arr[10])
                    //微信公众号支付也改为02  update by dhj 20161108
                    || GatewaySource.GATEWAY_SOURCE_15.value.equals((String) arr[10])
                    || GatewaySource.GATEWAY_SOURCE_18.value.equals((String) arr[10])) {
				r.setPaySource("02");
			} else if (GatewaySource.GATEWAY_SOURCE_05.value.equals((String) arr[10])) {
				r.setPaySource("05");
			/* 删除基金来源，走01 baiyunpeng at20161121
			 * } else if(GatewaySource.GATEWAY_SOURCE_11.value.equals((String)arr[10])){
                r.setPaySource("11");*/
            }else if(GatewaySource.GATEWAY_SOURCE_08.value.equals((String) arr[10])){//生钱包单独统计来源 modify by tangyang at 20160907
                r.setPaySource("08");
                // add by sh.jiao 20171030 增加委托代收的交易来源 12-api接口 start
            } else if(GatewaySource.GATEWAY_SOURCE_19.value.equals((String) arr[10])){
                r.setPaySource("12");
                // end
            } else if(GatewaySource.GATEWAY_SOURCE_20.value.equals((String) arr[10])){//兜兜平台支付来源修改 modify by liuxuyang at 20171017
                r.setPaySource("20");
            } else if(GatewaySource.GATEWAY_SOURCE_21.value.equals((String) arr[10])){
                r.setPaySource("21");
            } else if(GatewaySource.GATEWAY_SOURCE_22.value.equals((String) arr[10])){//E商通 modify by liuxuyang at 20180403
                r.setPaySource("22");
            } else {
				r.setPaySource("01");
	          //  log.info("其他支付来源");
			}
         //   log.info("---------------------网关来源:"+r.getPaySource());
            r.setRemark1((String) arr[29]);
            if(arr[30]!=null){//退款手续费
                r.setRefundFeeAmt(((BigDecimal)arr[30]).toString());
            }
            //客户来源
            r.setUserSource((String) arr[31]);
            //交易总金额
            r.setTotalOrderAmt(((BigDecimal)arr[32]).toString());

            //金额修改-增加结算金额和手续费承担方字段-jiaoshuai
            //结算金额
            if(arr[33] != null) {
                r.setSettleAmt(((BigDecimal)arr[33]).toString());
            }
            //手续费承担方
            if(arr[34] != null) {
                r.setOrderFeePayer((String)arr[34]);
            }
            if(arr[40] != null){
                r.setOldPayChannel((String)arr[40]);
            }
            //交易批次号
            if(arr[41] !=null){
            	r.setTransBatchNo((String)arr[41]);
            }
            if(null != arr[42]){
                r.setMainRecvAcctProdName((String)arr[42]);
            }
            if(null != arr[43]){
                r.setMainPayAcct((String)arr[43]);
            }
            orderList.add(r);
        }
        return orderList;
    }

    /**
     * 初始化 收付款银行名称
     * @param r
     * @param arr
     */
    private void initRecvPayBankName(TransQueryResultVO r, Object[] arr) {
        if(OrderType.ORDER_TYPE_02.value.equals((String)arr[36])){//付款类订单
            //付款银行
            //线上付款
            if(arr[14]!=null){
                r.setMainPayAcct((String)arr[14]);
            }
            //线下付款
            if(arr[37] != null){
                r.setMainPayAcct((String)arr[37]);
            }
            //收款银行
            if(arr[35]!=null){//收款银行名称
                r.setMainRecvAcctProdName((String)arr[35]);
            }
        }else if(IntTxnCd._01200.value.equals(r.getTransTypeId()) || IntTxnCd._01203.value.equals(r.getTransTypeId())){
            if(arr[38] != null) {
                r.setMainPayAcct((String) arr[38]);
            }
        }else if(IntTxnCd._00500.value.equals(r.getTransTypeId())){
            if(PayChannel._49.value.equals(r.getPayChannel()) || PayChannel._98.value.equals(r.getPayChannel())){
                //线下充值
                if(arr[14]!=null){
                    r.setMainRecvAcctProdName((String)arr[14]);
                }
            }else {
                if(arr[14]!=null) {
                    r.setMainPayAcct((String) arr[14]);
                }
                if(arr[39]!=null){
                    r.setMainRecvAcctProdName((String)arr[39]);
                }
            }
        }else if(OrderType.ORDER_TYPE_01.value.equals((String)arr[36])){
            if(arr[14]!=null){
                r.setMainPayAcct((String)arr[14]);
            }
            if(arr[39]!=null){
                r.setMainRecvAcctProdName((String)arr[39]);
            }
        }else{
            //其它类型订单
            //收款银行
            if(arr[14]!=null){
                r.setMainRecvAcctProdName((String)arr[14]);
            }
        }
    }
    
    /**
     * 翻译订单处理状态
     * @param stepNum
     * @param decision
     * @param done
     * @param tranSts
     * @return
     */
    private String translateOrderHandleSts(int stepNum,int decision,int done,int tranSts){
        String handleSts=null;
        if(stepNum==1&&decision==0){
            return "待审核";
        }        
        
        if(stepNum==1||stepNum==2){            
            if(done==1){
                return "成功";
            }else{
                if(DecisionValue.AGREE.value==decision){
                    return "待确认";
                }else if(DecisionValue.REJECT.value==decision){
                    return "成功";
                }
            }
        }else if(stepNum==-1){
            handleSts=TxnStaCode.getCategory(tranSts);
            if("进行中".equals(handleSts)){
                handleSts="处理中";
            }
        }
        
        return handleSts;
    }
    
    
    
    /**
     * 获取银行订单号
     * @param gopayOrderId
     * @throws GopayException
     */
    private String getBankOrderId(String gopayOrderId){
        String bankOrderId=null;
        
        StringBuilder sql = new StringBuilder("");
        sql.append(" select m.gopay_order_id, ");
        sql.append("  nvl(bo.gopay_out_order_id, bo2.gopay_out_order_id) bankOrderId ");
        sql.append("  from cps_gen_main_order m ");
        sql.append("  left join cps_gen_main_order m2 on m2.relevance_gopay_order_id =m.gopay_order_id ");
        sql.append("  left join bis_bank_main_order bo on bo.gopay_order_id = m.gopay_order_id ");
        sql.append("  left join bis_bank_main_order bo2 on bo2.gopay_order_id =m2.gopay_order_id ");
        sql.append(" where m.gopay_order_id = :gopayOrderId ");
        
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("gopayOrderId", gopayOrderId);
        
        // 执行查询
        List<Object[]> rsList = commonQueryDAO.findBySql(sql.toString(), map);
        if(rsList!=null&&rsList.size()>0){
            Object[] arr=rsList.get(0);
            bankOrderId=(String)arr[1];
        }

        return bankOrderId;
    }


    @Override
    public List<TransQueryResultVO> getMainOrdersByOrderOrderStlmTime(String ymd, int pageNo, int pageSize) {
        DbContextHolder.setDbType(DBType.DS_ONLY_READ.value);
        ymd = ymd.trim();
        StringBuilder sql = new StringBuilder("");
        sql.append(" select m.GOPAY_TXN_TM, ");
        sql.append(" m.GOPAY_ORDER_ID, ");
        sql.append(" m.GOPAY_INT_TXN_CD, ");
        sql.append(" m.MER_ID, ");
        sql.append(" m.MER_ORDER_ID, ");
        sql.append(" m.MER_TXN_AMT, ");
        sql.append(" m.ORDER_REAL_FEE_AMT, ");
        sql.append(" m.OUT_STLM_ID, ");
        sql.append(" m.ORDER_STLM_TIME, ");
        sql.append(" m.PAY_CHANNEL, ");
        sql.append(" m.GATEWAY_SOURCE, ");//10
        sql.append(" m.TXN_STA_CD, ");
        sql.append(" m.REVERSAL_FLAG, ");
         /*console优化 新交易查询退款交易显示原银行订单号 added by Chenyu Li at 2017/12/14 start*/
        sql.append("    case" +
                "          when (m.relevance_gopay_order_id is not null and m.gopay_int_txn_cd in ('01200','01201','01202','01203','00701','01610','02012','02015')) then" +
                "           (select bi3.gopay_out_order_id from bis_bank_main_order bi3 where bi3.gopay_order_id = m.relevance_gopay_order_id)" +
                "          else" +
                "           bo.gopay_out_order_id" +
                "        end AS gopay_out_order_id, ");
         /*console优化 新交易查询退款交易显示原银行订单号 added by Chenyu Li at 2017/12/14 end*/
        sql.append(" bi.NAME, ");
        sql.append(" c1.CUST_NAME payCustName, ");
        sql.append(" c1.EMAIL payEmail, ");
        sql.append(" m.pay_acct, ");
        sql.append(" c2.CUST_NAME recvCustName, ");
        sql.append(" c2.EMAIL recvEmail, ");
        sql.append(" m.main_recv_acct, ");//20
        sql.append(" c3.CUST_NAME merName, ");
        sql.append(" a.prod_code, ");
        sql.append(" nvl(wav.STEP_NUM, -1), ");
        sql.append(" nvl(wav.DECISION, -1), ");
        sql.append(" nvl(wav.DONE, -1), ");
        sql.append(" m.child_tran_type, ");
        sql.append(" m.mer_txn_tm, ");
        //付款渠道[线上(自动)、线上、线下]
        sql.append(" case when(bo.GOPAY_OUT_ORDER_ID is not null and (gpo.AUTO_SETTLE is null or gpo.AUTO_SETTLE = '"+CpsGenPayOrderConstants.AUTO_SETTLE_NO+"')) then '02' ");
        sql.append(" when gpo.AUTO_SETTLE = '"+CpsGenPayOrderConstants.AUTO_SETTLE_YES+"' then '01' ");
        sql.append(" else (case when(gpo.OFFLINE_BANK_ORDER_NUM is not null or (wav.STEP_NUM = 2 and wav.DECISION =1)) then '03' else '' end ) end AS payType,");
        sql.append(" m.remark1, ");
        sql.append(" ga.refund_fee_amt, ");//30
        sql.append(" u.USER_SOURCE ,");
        sql.append(" m.TOTAL_ORDER_AMT, ");
        //金额修改-增加结算金额和手续费承担方字段-jiaoshuai
        sql.append(" m.SETTLE_AMT,"); //结算金额
        sql.append(" m.ORDER_FEE_PAYER, ");//手续费承担方
        sql.append(" gpo.RECV_BANK_NAME as RECV_BANK_NAME, ");  //付款银行  add by zhaojt
        sql.append(" m.order_type as orderType, ");  //订单类型  add by zhaojt
        sql.append(" bim.NAME as offline_bank_name, ");  //线下付款银行  add by zhaojt
        sql.append(" mv2.BANK_NAME as ORI_RECV_BANK_NAME, ");    //原交易银行  add by zhaojt
        sql.append(" bi2.NAME as BI_BANK_NAME, ");    //银行订单  add by zhaojt
        sql.append(" cg.pay_channel as oldPayChannel, ");    //原支付方式  add by zhaoxubing
        sql.append(" bo.TRANS_BATCH_NO "); //交易批次号 add by wjq
        
        
        sql.append(" from CPS_GEN_MAIN_ORDER m ");
        sql.append("  LEFT JOIN CPS_GEN_MAIN_ORDER cg on m.relevance_gopay_order_id=cg.gopay_order_id ");
        sql.append(" left join MAIN_ORDER_RECEIPT_VIEW mv2 on m.RELEVANCE_GOPAY_ORDER_ID = mv2.gopay_order_id ");
        sql.append(" left join bis_bank_main_order bo on bo.gopay_order_id = m.gopay_order_id ");
        sql.append(" left join dic_gopay_bank_info bi on m.out_stlm_id = bi.ABBR_CODE ");
        sql.append(" left join dic_gopay_bank_info bi2 on bo.RECV_BANK = bi2.ABBR_CODE ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c1 on c1.CUST_ID = m.pay_cust_id ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c2 on c2.CUST_ID = m.main_recv_cust_id ");
        sql.append(" left join GOPAY_ALL_CUST_INFO c3 on c3.CUST_ID = m.mer_id ");
        sql.append(" left join ACPS_ACCT_MAST a on a.acct_id = m.pay_acct ");
        sql.append(" left join ACPS_ACCT_MAST a1 on a1.acct_id = m.main_recv_acct ");
        sql.append(" left join CPS_GEN_ASSIST_ORDER ga on ga.gopay_order_id = m.gopay_order_id ");
        sql.append(" left join (select v.target_id, ");
        sql.append("                   v.step_num, ");
        sql.append("                   v.decision, ");
        sql.append("                   v.done, ");
        sql.append("                   v.task_id, ");
        sql.append("                   max(v.task_id) OVER(PARTITION BY v.target_id) maxTaskId ");
        sql.append("            from WF_APPR_VIEW v) wav on (wav.target_id = m.gopay_order_id and ");
        sql.append("                                wav.task_id >= wav.maxTaskId) ");
        sql.append(" left join CPS_GEN_PAY_ORDER gpo on m.gopay_order_id = gpo.gopay_order_id ");//新增付款渠道条件
        sql.append(" left join dic_gopay_bank_info bim on gpo.offline_gopay_bank_code = bim.ABBR_CODE ");//新增线下付款银行
      //一帐通 新增 客户来源 (2015/12/9)
        sql.append(" left join USER_INFO u on u.CUST_ID = m.pay_cust_id ");
        sql.append(" where 1 = 1 ");
        sql.append(" and ((m.gopay_int_txn_cd = '01302' and");
        sql.append(" m.relevance_gopay_order_id is null) or");
        sql.append(" m.gopay_int_txn_cd <> '01302')");
        sql.append(" and(m.order_stlm_time >= to_date('"+ymd+" 00:00:00', 'yyyymmdd hh24:mi:ss')");
        sql.append(" and m.order_stlm_time < to_date('"+ymd+" 00:00:00', 'yyyymmdd hh24:mi:ss') + 1");
        sql.append(" or (m.order_stlm_time is null and m.GOPAY_ORDER_ID like '"+ymd+"%'))");
        sql.append("and m.gopay_int_txn_cd not like '017%'  and (nvl(a1.prod_code,0)!= 'KJRMBZH'  and nvl(a.prod_code,0)!= 'KJRMBZH' and  nvl(a1.prod_code,0)!= 'KJWKZH'  and nvl(a.prod_code,0)!= 'KJWKZH'  )");
        //屏蔽掉跨境的订单,add by qld 20161008,跨境的订单就不用进入读写分离的表了   20171101加了KJWKZH
        // 执行查询
        List<Object[]> rsList = commonQueryDAO.findBySql(sql.toString(), pageNo, pageSize);
        
        // 封装查询结果
        return buildQueryResult(rsList);
    }

}
